package ca.cbc.giscommon.dao;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Formatter;
import java.util.List;

import com.vividsolutions.jts.geom.Geometry;

import ca.cbc.giscommon.entity.Content;
import ca.cbc.giscommon.entity.Source;
import ca.cbc.giscommon.entity.Zone;
import ca.cbc.giscommon.util.GeometryUtil;
import ca.cbc.giscommon.util.StringUtil;

public class ContentDAO extends EntityHome<Content>{

	@Override
	public String getFullClassName() {
		return Content.class.getCanonicalName();
	}
	
	public void delete(Source source) {
		getCurrentSession().createQuery("DELETE Content c WHERE c.source = :source")
		.setEntity("source", source)
		.executeUpdate();
	}
	
    public List<Content> listWithZone() {
    	return this.listWithZone(null, null, null, -1l, -1l, "pubdate", false, 50, 0);
    }
    
    @SuppressWarnings("unchecked")
	public List<Content> listWithZone(String zoneIds, Geometry bbox, String categoryIds, long startTime, long endTime, String orderBy, boolean isAscending, int limit, int offset) {
    	StringBuilder queryBuilder = null;
    	if(!StringUtil.instance.isNullOrEmpty(categoryIds)) {
    		queryBuilder = new StringBuilder("SELECT {s.*}, {z.*} FROM zone z, content s, category c, source so WHERE so.category_id = c.id and ST_Intersects(z.the_geom, s.the_geom)");
    		queryBuilder.append(" and s.source_id = so.id and c.id in (").append(categoryIds).append(")");
    	} else {
    		queryBuilder = new StringBuilder("SELECT {s.*}, {z.*} FROM zone z, content s WHERE ST_Intersects(z.the_geom, s.the_geom)");
    	}
    	
    	if(!StringUtil.instance.isNullOrEmpty(zoneIds)) {
    		queryBuilder.append(" and z.id in (").append(zoneIds).append(")");
    	} else if (bbox != null) {
    		queryBuilder.append(new Formatter().format(" and ST_Intersects(ST_GeomFromText('%s', %d), s.the_geom)", bbox.toString(), GeometryUtil.instance.getSRID()).toString());
    	}    	
    	
    	if(startTime > 0l) {
    		queryBuilder.append(" and pubdate >= '").append(new Timestamp(startTime)).append("'");
    	}
    	
    	if(endTime > 0l) {
    		queryBuilder.append(" and pubdate <= '").append(new Timestamp(endTime)).append("'");
    	}	
    	
    	queryBuilder.append(new Formatter().format(" ORDER BY s.%s %s", orderBy, getAscOrDesc(isAscending)));
    	
    	if(limit > 0) {
    		queryBuilder.append(" LIMIT " + limit);
    	}
    	
    	if(offset > 0) {
    		queryBuilder.append(" OFFSET " + offset);
    	}
    	
    	List<Object> storyAndZones = getCurrentSession().createSQLQuery(queryBuilder.toString()).addEntity("s", Content.class).addEntity("z", Zone.class).list();
        List<Content> contents = new ArrayList<Content>();
        for (Object obj : storyAndZones) {
            Object[] objArray = (Object[]) obj;
            if (objArray.length != 2) {
                logger.error("Error! Something is wrong when fetch contents {}.", Arrays.toString(objArray));
                continue;
            }

            Content content = (Content) objArray[0];
            Zone zone = (Zone) objArray[1];
            if(contents.contains(content)) {
            	content = contents.get(contents.indexOf(content));
            	content.addZone(zone);
            } else {
	            content.addZone(zone);
	            contents.add(content);
            }
        }
        return contents;
    }
    
//	private ZoneDAO zoneDAO = new ZoneDAO();
//	
//	/**
//	 * 
//	 * @param transientInstance
//	 */
//	public void saveOrUpdate(Content content) {
//		this.setZoneIfExist(content);
//		super.saveOrUpdate(content);
//    }
//	
//	private void setZoneIfExist(Content content) {
//		Geometry geo = content.getTheGeom();
//		if(geo != null) {
//			content.setZones(zoneDAO.getZones(geo));
//		}
//	}
//	
//	/**
//	 * 
//	 * @param transientInstance
//	 */
//	public void save(Content content) {
//		this.setZoneIfExist(content);
//		super.save(content);
//    }
//
//    public List<Content> list(boolean isWithZone) {
//    	return this.list(isWithZone, null, null, null, -1l, -1l, "publishDate", false, 50, 0);
//    }
//    
//    public List<Content> list(String zoneIds, Geometry bbox, String categoryIds, long startTime, long endTime, String orderBy, boolean isAscending, int limit, int offset) {
//    	return this.list(true, zoneIds, bbox, categoryIds, startTime, endTime, orderBy, isAscending, limit, offset);
//    }
//    
//    @SuppressWarnings("unchecked")
//	public List<Content> list(boolean isWithZone, String zoneIds, Geometry bbox, String categoryIds, long startTime, long endTime, String orderBy, boolean isAscending, int limit, int offset) {
//    	Criteria criteria = getCurrentSession().createCriteria(Content.class);
//    	if(isWithZone) {
//    		criteria.add(Restrictions.isNotNull("zones")).add(Restrictions.isNotEmpty("zones"));
//    	}
//    	
//    	if(!StringUtil.instance.isNullOrEmpty(orderBy)) {
//	    	if(isAscending) {
//	    		criteria.addOrder(Order.asc(orderBy));
//	    	} else {
//	    		criteria.addOrder(Order.desc(orderBy));
//	    	}
//    	}
//    	
//    	if(!StringUtil.instance.isNullOrEmpty(zoneIds)) {   		
//    		criteria.createCriteria("zones").add(Restrictions.in("id", StringUtil.instance.parseIds(zoneIds)));
//    	} else if (bbox != null) {
//    		String query = new Formatter().format("ST_Intersects(ST_GeomFromText('%s', %d), this_.the_geom)", bbox.toString(), GeometryUtil.instance.getSRID()).toString();
//    		criteria.add(Restrictions.sqlRestriction(query));
//    	}
//    	
//    	if(!StringUtil.instance.isNullOrEmpty(categoryIds)) {   		
//    		criteria.createCriteria("source").createCriteria("category").add(Restrictions.in("id", StringUtil.instance.parseIds(categoryIds)));
//    	}
//    	
//    	if(startTime > 0l) {
//    		criteria.add(Restrictions.ge("publishDate", new Timestamp(startTime)));
//    	}
//    	
//    	if(endTime > 0l) {
//    		criteria.add(Restrictions.le("publishDate", new Timestamp(endTime)));
//    	}
//    	
//    	if(limit > 0) {
//    		criteria.setMaxResults(limit);
//    	}
//    	
//    	if(offset > 0) {
//    		criteria.setFirstResult(offset);
//    	}
//    	
//    	return criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();
//    }
}
